﻿using SqlBuilder.Model;
using System;
using System.Collections.Generic;
using System.Data;
namespace SqlBuilder.Code
{
    public class SqlBuilderService
    {
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MTempAllInfo QueryAllInfo(string id)
        {
            using (IDbConnection conn = SQLiteHelper.Current.GetConn())
            {
                SqlBuilderDAL dal = new SqlBuilderDAL();
                MTempAllInfo model = new MTempAllInfo();
                model.SQLMap = dal.QuerySqlMapById(conn, id);
                if (model.SQLMap!=null&&model.SQLMap.Count>0)
                {
                    foreach (var item in model.SQLMap)
                    {
                        if (!string.IsNullOrEmpty(item.script))
                        {
                            item.script = Base64.Base64Decode(item.script);
                        }
                    }
                }
                model.SQLTemplate = dal.QuerySQLTemplateById(conn, id);
                return model;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<MSQLTemplate> QueryTempLates(string name)
        {
            using (IDbConnection conn = SQLiteHelper.Current.GetConn())
            {
                SqlBuilderDAL dal = new SqlBuilderDAL();

                return dal.QuerySQLTemplate(conn, name);
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string Save(MTempAllInfo model)
        {
            IDbConnection conn = null;
            IDbTransaction trans = null;
            string tempid = string.Empty;
            try
            {
                conn = SQLiteHelper.Current.GetConn();
                trans = conn.BeginTransaction();
                SqlBuilderDAL dal = new SqlBuilderDAL();
                if (!string.IsNullOrEmpty(model.SQLTemplate.id))
                {
                    if (!dal.UpdateSQLTemplate(conn, trans, model.SQLTemplate))
                    {
                        throw new Exception("更新失败");
                    }

                }
                else
                {
                    model.SQLTemplate.id = Guid.NewGuid().ToString();

                    if (!dal.AddSQLTemplate(conn, trans, model.SQLTemplate))
                    {
                        throw new Exception("添加失败");
                    }
                }

                dal.DeleteSQlMapsByTId(conn, trans, model.SQLTemplate.id);

                if (model.SQLMap != null && model.SQLMap.Count > 0)
                {
                    foreach (var item in model.SQLMap)
                    {
                        item.id = Guid.NewGuid().ToString();
                        item.templateId = model.SQLTemplate.id;
                        item.script = Base64.Base64Code(item.script);
                    }

                    dal.AddSqlMaps(conn, trans, model.SQLMap);
                }

                trans.Commit();
                tempid = model.SQLTemplate.id;
            }
            catch (Exception ex)
            {
                trans.Rollback();

            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

            return tempid;
        }

        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteTempLate(string id)
        {
            IDbConnection conn = null;
            IDbTransaction trans = null;
            bool issuccess = false;
            try
            {
                conn = SQLiteHelper.Current.GetConn();
                trans = conn.BeginTransaction();
                SqlBuilderDAL dal = new SqlBuilderDAL();
                dal.DeleteSQLTemplate(conn, trans, id);
                dal.DeleteSQlMapsByTId(conn, trans, id);
                trans.Commit();
                issuccess = true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

            return issuccess;
        }
    }
}
